﻿using SupermarketSalesModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SupermarketSalesController
{
    public class SQLServerDAOSale : DAOSale
    {

        public void add(Sale v)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO G3_SALE (idSalesClerk,idCR,idClient,status,paidAmount,discount,wonPoints," +
                    "totalAmountafterDiscount,totalAmount,subTotalAmount,saleDate) VALUES (@param1,@param2,@param3," +
                    "@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11)";

                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param6 = new SqlParameter("@param6",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param7 = new SqlParameter("@param7",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param8 = new SqlParameter("@param8",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param9 = new SqlParameter("@param9",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param10 = new SqlParameter("@param10",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param11 = new SqlParameter("@param11",
                                                        System.Data.SqlDbType.Date);

                param1.Value = v.Clerk.IdClerk;
                //param1.Value = 1;
                param2.Value = v.CashReg.Id;
                //param2.Value = 2;
                if (v.Client != null) param3.Value = v.Client.IdClient;
                else param3.Value = 0;
               // param3.Value = 3;
                param4.Value = v.Flag;
                param5.Value = v.PaidAmount;
                param6.Value = v.Descuento;
                param7.Value = v.WonPoints;
                param8.Value = v.TotalAmountAfterDiscount;
                param9.Value = v.TotalAmount;
                param10.Value = v.SubTotalAmount;
                param11.Value = v.SaleDate;

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);
                comm.Parameters.Add(param6);
                comm.Parameters.Add(param7);
                comm.Parameters.Add(param8);
                comm.Parameters.Add(param9);
                comm.Parameters.Add(param10);
                comm.Parameters.Add(param11);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }

            //PARA EL DETALLE

            for (int i = 0; i < v.Details.Count; i++)
            {

                conn = null;
                try
                {
                    //Paso 1 y 2: Registrar el driver y obtener la conexión
                    conn = new SqlConnection();
                    conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                        "Database=inf282;User ID=inf282;Password=inf282db;";
                    conn.Open();

                    //Paso 3. Preparar la sentencia
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = "INSERT INTO G3_SALESDETAIL (quantity,subTotal,discount,idSale,idProduct)" +
                        " VALUES (@param2,@param3,@param4,@param5,@param6)";

                    SqlParameter param2 = new SqlParameter("@param2",
                                                            System.Data.SqlDbType.Int);
                    SqlParameter param3 = new SqlParameter("@param3",
                                                            System.Data.SqlDbType.Float);
                    SqlParameter param4 = new SqlParameter("@param4",
                                                            System.Data.SqlDbType.Float);
                    SqlParameter param5 = new SqlParameter("@param5",
                                                            System.Data.SqlDbType.Int);
                    SqlParameter param6 = new SqlParameter("@param6",
                                                            System.Data.SqlDbType.Int);

                    SalesDetail sd = (v.Details)[i];

                    param2.Value = sd.Quantity;
                    param3.Value = sd.SubTotal;
                    param4.Value = sd.Descuento;
                    param5.Value = SalesManager.getSizeSale() - 1;
                    param6.Value = sd.Product.Id;

                    comm.Parameters.Add(param2);
                    comm.Parameters.Add(param3);
                    comm.Parameters.Add(param4);
                    comm.Parameters.Add(param5);
                    comm.Parameters.Add(param6);

                    //Paso 4. Ejecutar la sentencia
                    comm.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    // TODO Auto-generated catch block			
                    Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                    throw e;
                }
                finally
                {
                    //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                    try { if (conn != null) conn.Close(); }
                    catch (Exception e) { Console.WriteLine(e.StackTrace); }
                }
            }

        }

        public void update(int idSale, int status)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "UPDATE G3_SALE SET status=@param1" +
                    " WHERE idSale=@param2";

                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.Int);

                param1.Value = status;
                param2.Value = idSale;

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public Sale queryById(int idSale)
        {
            Sale v = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_SALE WHERE idSale=@param1";

                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);

                param1.Value = idSale;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    v = new Sale();

                    v.Clerk=SalesManager.getSalesclerkbyId(Int32.Parse(myReader["idSalesClerk"].ToString()));//llamar al metodo buscar cajero por id con el parametro (Int32.Parse(myReader["id_SalesClerk"].ToString())) 

                    v.CashReg = SalesManager.getCRbyId(Int32.Parse(myReader["idCR"].ToString()));

                    v.Client = SalesManager.getClientbyId(Int32.Parse(myReader["idClient"].ToString()));//llamara al metodo buscar cliente por id con el parametro (Int32.Parse(myReader["idClient"].ToString()))

                    v.Flag = Int32.Parse(myReader["status"].ToString());

                    v.PaidAmount = Double.Parse(myReader["paidAmount"].ToString());

                    v.Descuento = Double.Parse(myReader["discount"].ToString());

                    v.WonPoints = Int32.Parse(myReader["wonPoints"].ToString());

                    v.TotalAmountAfterDiscount = Double.Parse(myReader["totalAmountafterDiscount"].ToString());

                    v.TotalAmount = Double.Parse(myReader["totalAmount"].ToString());

                    v.SubTotalAmount = Double.Parse(myReader["subTotalAmount"].ToString());

                    v.SaleDate = DateTime.Parse(myReader["saleDate"].ToString());

                    v.Id = Int32.Parse(myReader["idSale"].ToString());

                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }

            List<SalesDetail> list = new List<SalesDetail>();
            conn = null;
            myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_SALESDETAIL WHERE idSale=@param1";

                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);

                param1.Value = idSale;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    SalesDetail sd = new SalesDetail();

                    sd.Id = Int32.Parse(myReader["idSD"].ToString());
                    sd.Quantity = Int32.Parse(myReader["quantity"].ToString());
                    sd.SubTotal = Double.Parse(myReader["subTotal"].ToString());
                    sd.Descuento = Double.Parse(myReader["discount"].ToString());
                    sd.IdSale = Int32.Parse(myReader["idSale"].ToString());
                    sd.Product = SalesManager.getProductbyId(Int32.Parse(myReader["idProduct"].ToString()));

                    list.Add(sd);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }

            v.Details = list;

            return v;
        }

        public int count()
        {
            int quantity = 0;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Count(1) AS quantity FROM G3_SALE";

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    quantity = Int32.Parse(myReader["quantity"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return quantity;
        }

    }
}
